mysql使用查询结果作为临时表

最新推荐文章于 2024-07-29 03:50:58 发布

qianmacao

最新推荐文章于 2024-07-29 03:50:58 发布

阅读量8.1w

点赞数 6

分类专栏： mysql

本文链接：https://blog.csdn.net/findmyself_for_world/article/details/42123201

版权

mysql 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

（一）select查询作为临时表

select cou,name from (select count(*) AS cou,enabled AS name from `user` group by enabled ) as a where cou>0

上面的语句就是将查询结果作为临时表，然后再在这个临时表中查找满足条件的数据。

语法: （注意：AS 可省略）

CODE:

SELECT ... FROM (subquery) AS tableName ...

先创建一个表:

CODE:

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);

我们就可以进行以下的嵌套查询了:

CODE:

SELECT sb1,sb2,sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;

结果是： 2, '2', 4.0.

SELECT AVG(sum_column1)
FROM (SELECT SUM(column1) AS sum_column1
FROM t1 GROUP BY column1) AS t1;

（二）行的子查询（Row Subquery）

SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

在该例子中，在保证子查询返回单一行数据的前提下，如果 column1=1 且 column2=2 ，则该查询结果为 TRUE。

MySQL 行构造符

在上面的例子中，WHERE 后面的 (1,2) 被称为行构造符，也可以写作 ROW(1，2)。行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。

SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)注意：[SQL] SELECT * FROM `user` WHERE ROW(username,`password`) = (SELECT username, `password` FROM `user`);
[Err] 1242 - Subquery returns more than 1 row
这是因为查询结果多于一行，可以这样处理
SELECT * FROM `user` WHERE ROW(username,`password`) = ANY(SELECT username, `password` FROM `user`);

（三）.使用Exist和Not Exist参数

SELECT <span style="color:#ff0000;">DISTINCT</span> store_type FROM Stores
                    WHERE <span style="color:#ff0000;">EXISTS</span> (SELECT * FROM Cities_Stores
                                  WHERE Cities_Stores.store_type = Stores.store_type);

SELECT DISTINCT store_type FROM Stores
                       WHERE <span style="color:#cc0000;">NOT EXISTS</span> (SELECT * FROM Cities_Stores
                                   WHERE Cities_Stores.store_type = Stores.store_type);

（四）条件关联关系查询

SELECT column1 FROM t1 AS x
     WHERE x.column1 = (SELECT column1 FROM t2 AS x
          WHERE x.column1 = (SELECT column1 FROM t3
               WHERE x.column2 = t3.column1));

    跟其他数据库做法是一样的。